<?php
include_once("ConnectionManager.php");
/*------------------------------------------------------------CATEGORIES------------------------------------------------------------*/
//Get root category (no parent)
function getAllRootCategory(){
    $query = "SELECT CAT_ID, CAT_NAME, (SELECT CAT_NAME FROM MITI_CATEGORY WHERE CAT_ID = C.CAT_PARENT_ID) AS PARENT_CAT_NAME, CAT_ORDER 
FROM MITI_CATEGORY C WHERE CAT_PARENT_ID = 0 ORDER BY CAT_ORDER, CAT_NAME ASC";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();    
}

function getTotalCategory(){
    $query = "SELECT COUNT(CAT_ID) AS QTY FROM MITI_CATEGORY";
    ConnectionManager::getInstance()->setQuery($query);
    $array = ConnectionManager::getInstance()->query();
    $result = mysql_fetch_array($array);
    return $result["QTY"];
}
//Get children category of category
function getAllChildrenOfCategory($cat_id){
    $query = "SELECT CAT_ID, CAT_NAME, (SELECT CAT_NAME FROM MITI_CATEGORY WHERE CAT_ID = C.CAT_PARENT_ID) AS PARENT_CAT_NAME, CAT_ORDER 
FROM MITI_CATEGORY C WHERE CAT_PARENT_ID = $cat_id ORDER BY CAT_ORDER, CAT_NAME ASC";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

//Get all Category no condition
function getAllCategory(){
    $query = "SELECT CAT_ID, CAT_NAME, (SELECT CAT_NAME FROM MITI_CATEGORY WHERE CAT_ID = C.CAT_PARENT_ID) AS PARENT_CAT_NAME, CAT_ORDER 
FROM MITI_CATEGORY C ORDER BY CAT_PARENT_ID, CAT_NAME";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

//Check if Category has children
function hasChild($cat_id){
    $query = "SELECT COUNT(CAT_ID) FROM MITI_CATEGORY WHERE CAT_PARENT_ID = $cat_id";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();	$row = mysql_fetch_array($result);
    return $row[0] > 0;
}

//Get one Category Information
function getCategory($cat_id){
    $query = "SELECT CAT_ID, CAT_NAME, CAT_PARENT_ID, (SELECT CAT_NAME FROM MITI_CATEGORY WHERE CAT_ID = C.CAT_PARENT_ID) AS PARENT_CAT_NAME, CAT_ORDER 
FROM MITI_CATEGORY C WHERE CAT_ID = $cat_id";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
	$ret = null;
    if(mysql_num_rows($result) > 0) 
		$ret = mysql_fetch_array($result);
	return $ret;
}

//Create Category
function createCategory($cateTitle, $cateParent, $order){
    $query = "INSERT INTO MITI_CATEGORY(CAT_NAME, CAT_PARENT_ID, CAT_ORDER) VALUES('$cateTitle',$cateParent,$order)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

//Update Category
function updateCategory($cat_id, $cat_name, $cat_parent_id,$order){
    $query = "UPDATE MITI_CATEGORY SET CAT_NAME = '$cat_name', CAT_PARENT_ID=$cat_parent_id, CAT_ORDER=$order WHERE CAT_ID = $cat_id";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

//Assign Attribute to Category
function assignAttToCate($cateId,$attId){
    $query = "INSERT INTO MITI_CATEGORY_ATTRIBUTE(CAT_ID, ATT_ID) VALUES($cateId,$attId)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

//Remove all attributes in update Category
function unassginAllAttributeOfCategory($cat_id){
    $query = "DELETE FROM MITI_CATEGORY_ATTRIBUTE WHERE CAT_ID = $cat_id";
 	$query_all_record = "SELECT * FROM MITI_CATEGORY_ATTRIBUTE WHERE CAT_ID = $cat_id";
    ConnectionManager::getInstance()->setQuery($query_all_record);
    $records = ConnectionManager::getInstance()->query();
    while($row = mysql_fetch_array($records)){
        $query = "DELETE FROM MITI_CATEGORY_ATTRIBUTE WHERE CAT_ID = $cat_id AND ATT_ID = ".$row["ATT_ID"];
        ConnectionManager::getInstance()->setQuery($query);
        $result = ConnectionManager::getInstance()->query();
    }
    return true;
}

//Remove all options in update Category
function unassginAllOptionOfCategory($cat_id){
    $query_all_record = "SELECT * FROM MITI_CATEGORY_OPTIONAL_PRICE WHERE CAT_ID = $cat_id";
    ConnectionManager::getInstance()->setQuery($query_all_record);
    $records = ConnectionManager::getInstance()->query();
    while($row = mysql_fetch_array($records)){
        $query = "DELETE FROM MITI_CATEGORY_OPTIONAL_PRICE WHERE CAT_ID = $cat_id AND OPP_ID =".$row["OPP_ID"];
        ConnectionManager::getInstance()->setQuery($query);
        $result = ConnectionManager::getInstance()->query();
    }
    return true;
}

//Check if Category is able to change Parent
function isAbleChangeParent($cat_id){
    $query1 = "SELECT COUNT(CAT_ID) FROM MITI_OPTIONAL_VALUE WHERE CAT_ID = $cat_id";
    $query2 = "SELECT COUNT(CAT_ID) FROM MITI_ATTRIBUTE_VALUE WHERE CAT_ID = $cat_id";
    ConnectionManager::getInstance()->setQuery($query1);
    $result1 = ConnectionManager::getInstance()->query();
    ConnectionManager::getInstance()->setQuery($query2);
    $result2 = ConnectionManager::getInstance()->query();
    //echo "result:".mysql_fetch_array($result1)[0]. "" .mysql_fetch_array($result2)[0] <= 0;	
    $row1 = mysql_fetch_array($result1);
    $row2 = mysql_fetch_array($result2);
    return $row1[0] <= 0 && $row2[0] <= 0;}

//Check if Category is able to be deleted

function isCateDeletable($cat_id){
	$query = "SELECT COUNT('PRO_ID') FROM MITI_PRODUCT WHERE CAT_ID = $cat_id";
	ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
	
	$query1 = "SELECT COUNT('CAT_ID') FROM MITI_CATEGORY_OPTIONAL_PRICE WHERE CAT_ID = $cat_id";
	ConnectionManager::getInstance()->setQuery($query1);
    $result1 = ConnectionManager::getInstance()->query();
	
	$query2 = "SELECT COUNT('CAT_ID') FROM MITI_CATEGORY_ATTRIBUTE WHERE CAT_ID = $cat_id";
	ConnectionManager::getInstance()->setQuery($query2);
    $result2 = ConnectionManager::getInstance()->query();

	$row1 = mysql_fetch_array($result);
    $row2 = mysql_fetch_array($result1);
    $row3 = mysql_fetch_array($result2);
    
	return $row1[0] <= 0 && $row2[0] <= 0 && $row3[0] <= 0;}

function deleteCategory($cat_id){
	$query = "DELETE FROM MITI_CATEGORY WHERE CAT_ID = $cat_id";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

// Create recursive categories
function getTreeOfCategories($parentId, $sep = "  ", $categoryData = null){
    if(!$categoryData) $categoryData = array();
    $query = "SELECT CAT_ID, CAT_NAME FROM MITI_CATEGORY WHERE CAT_PARENT_ID = $parentId";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    
    while($row = mysql_fetch_array($result)){
        $categoryData[] = array("id" => $row["CAT_ID"], "name" => $sep.$row["CAT_NAME"]);
        $categoryData = getTreeOfCategories($row["CAT_ID"], $sep."--  ", $categoryData); 
    }
    return $categoryData;
}

function getAllParentCategoriesOf($catId, $categories = null){
    if(!$categories) $categories = array();
    $query = "SELECT CAT_ID FROM MITI_CATEGORY WHERE CAT_ID IN (SELECT CAT_PARENT_ID FROM MITI_CATEGORY WHERE CAT_ID = $catId)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    
    while($row = mysql_fetch_array($result)){
        $categories[] = array("id" => $row["CAT_ID"]);
        $categories = getAllParentCategoriesOf($row["CAT_ID"], $categories);
    }
    return array_reverse($categories);
}

function findCategoryByAttribute($attributeId){
    $query = "SELECT CAT_ID FROM MITI_CATEGORY_ATTRIBUTE WHERE ATT_ID = $attributeId";
    ConnectionManager::getInstance()->setQuery($query);
    $row = ConnectionManager::getInstance()->fetch(ConnectionManager::getInstance()->query());
    return $row["CAT_ID"];
}

function findCategoryByOptionalPrice($optionalPriceId){
    $query = "SELECT CAT_ID FROM MITI_CATEGORY_OPTIONAL_PRICE WHERE OPP_ID = $optionalPriceId";
    ConnectionManager::getInstance()->setQuery($query);
    $row = ConnectionManager::getInstance()->fetch(ConnectionManager::getInstance()->query());
    return $row["CAT_ID"];
}

/*ATTRIBUTES*/
function getAllAttribute(){
    $query = "SELECT ATT_ID, ATT_NAME FROM MITI_ATTRIBUTE";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

//Get Attributes of Category
function getAttributesOfCategory($cat_id){
    $query = "SELECT ATT_ID, ATT_NAME FROM MITI_ATTRIBUTE WHERE ATT_ID IN (SELECT ATT_ID FROM MITI_CATEGORY_ATTRIBUTE CA WHERE CAT_ID = $cat_id)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

//Get Attributes not belong to Category (used when create new child category)
function getAvailableAttributeOfCategory($cat_id, $cat_prent_id){
    $query = "SELECT ATT_ID, ATT_NAME FROM MITI_ATTRIBUTE WHERE ATT_ID NOT IN (SELECT ATT_ID FROM MITI_CATEGORY_ATTRIBUTE CA WHERE CAT_ID = $cat_id OR CAT_ID = $cat_prent_id)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

//Attribute is deletable if it is not assigned to any category
function isAttDeleteTable($attId){
    $query = "SELECT COUNT(ATT_ID) FROM MITI_CATEGORY_ATTRIBUTE WHERE ATT_ID = $attId";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    $row = mysql_fetch_array($result);
    return $row[0] <= 0;
}

//Attribute is not able to be removed from category if there is value 
function isAttributeUnassignable($attId, $catId){
    $query = "SELECT COUNT(ATT_ID) FROM MITI_ATTRIBUTE_VALUE WHERE ATT_ID = $attId AND CAT_ID = $catId";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    $row = mysql_fetch_array($result);
    return $row[0] <= 0;
}

//Delete Attribute
function deleteAttribute($attId){
    $query = "DELETE FROM MITI_ATTRIBUTE WHERE ATT_ID = $attId";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

//Crate attribute
function createAttribute($attTitle){
    $query = "INSERT INTO MITI_ATTRIBUTE(ATT_NAME) VALUES('$attTitle')";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

/*------------------------------------------------------------OPTIONAL_PRICE------------------------------------------------------------*/
//Get Attributes of Category
function getOptionalPricesOfCategory($cat_id){
    $query = "SELECT OPP_ID, OPP_NAME FROM MITI_OPTIONAL_PRICE WHERE OPP_ID IN (SELECT OPP_ID FROM MITI_CATEGORY_OPTIONAL_PRICE COP WHERE CAT_ID = $cat_id)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function getOptionPriceByOptionItem($optionalPriceId){
    
    
}

/*------------------------------------------------------------OPTIONS------------------------------------------------------------*/
function getAllOption(){
    $query = "SELECT OPP_ID, OPP_NAME FROM MITI_OPTIONAL_PRICE";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

//Get Options of Category
function getOptionsOfCategory($cat_id){
	$query = "SELECT OPP_ID, OPP_NAME FROM MITI_OPTIONAL_PRICE WHERE OPP_ID IN (SELECT OPP_ID FROM MITI_CATEGORY_OPTIONAL_PRICE WHERE CAT_ID = $cat_id)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

//Get Available Option for Category
function getAvailableOptionOfCategory($catId,$cat_parent_id){
	$query = "SELECT OPP_ID, OPP_NAME FROM MITI_OPTIONAL_PRICE WHERE OPP_ID NOT IN (SELECT OPP_ID FROM MITI_CATEGORY_OPTIONAL_PRICE CO WHERE CAT_ID = $catId OR CAT_ID = $cat_parent_id)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}


//Create new Option
function createOption($optTitle){
	$query = "INSERT INTO MITI_OPTIONAL_PRICE(OPP_NAME) VALUES('$optTitle')";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

//Attribute is deletable if it is not assigned to any category
function isOptionDeletetable($optionid){
    $query1 = "SELECT COUNT(OPP_ID) FROM MITI_OPTION_ITEM WHERE OPP_ID = $optionid";
    ConnectionManager::getInstance()->setQuery($query1);
    $result1 = ConnectionManager::getInstance()->query();
    $row1 = mysql_fetch_array($result1);
    
    $query2 = "SELECT COUNT(OPP_ID) FROM MITI_CATEGORY_OPTIONAL_PRICE WHERE OPP_ID = $optionid";
    ConnectionManager::getInstance()->setQuery($query2);
    $result2 = ConnectionManager::getInstance()->query();
    $row2 = mysql_fetch_array($result2);
    
    return $row1[0] <= 0 && $row2[0] <= 0;
}

//Option is not able to be removed from category if there is value 
function isOptionUnassignable($optId, $catId){
    $query = "SELECT COUNT(ATT_ID) FROM MITI_ATTRIBUTE_VALUE WHERE OPP_ID = $optId AND CAT_ID = $catId";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    $row = mysql_fetch_array($result);
    return $row[0] <= 0;
}


function deleteOption($optId){
	$query = "DELETE FROM MITI_OPTIONAL_PRICE WHERE OPP_ID = $optId";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function assignOptToCate($cateId,$optId){
	$query = "INSERT INTO MITI_CATEGORY_OPTIONAL_PRICE(CAT_ID, OPP_ID) VALUES($cateId,$optId)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function getOptionById($optionid){
    $query = "SELECT * FROM MITI_OPTIONAL_PRICE WHERE OPP_ID = $optionid";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    if(mysql_num_rows($result) > 0){
        return mysql_fetch_array($result);
    }else{
        return null;    
    }
}

function updateOption($optionid,$title){
    $query = "UPDATE MITI_OPTIONAL_PRICE SET OPP_NAME = '$title' WHERE OPP_ID = $optionid";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}


/*OptionItem*/

function createOptionItem($optionid, $optionitemtitle){
    $query = "INSERT INTO MITI_OPTION_ITEM(OOI_NAME, OPP_ID) VALUES('$optionitemtitle',$optionid)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function getCategoryOfOption($optionid){
    $query = "SELECT * FROM MITI_CATEGORY WHERE CAT_ID IN (SELECT CAT_ID FROM MITI_CATEGORY_OPTIONAL_PRICE WHERE OPP_ID = $optionid)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function getOptionItemOfOption($optionid){
    $query = "SELECT * FROM MITI_OPTION_ITEM WHERE OPP_ID = $optionid";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function isOptionItemDeleteTable($optionitemid){
    $query = "SELECT COUNT(OPTIONITEMID) FROM MITI_RECEIPT_PRODUCT_OPTIONITEM WHERE OPTIONITEMID = $optionitemid";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    $row = mysql_fetch_array($result);
    return $row[0] <= 0;
}

function deleteOptionItem($optionitemid){
    $query = "DELETE FROM MITI_OPTION_ITEM WHERE OOI_ID = $optionitemid";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function findOptionItemOfOption($optionid){
    $query = "SELECT * FROM MITI_OPTION_ITEM WHERE OPP_ID = $optionid";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function findOptionItemByOptionalPrice($optionalPriceId){
    $query = "SELECT OOI_NAME FROM MITI_OPTION_ITEM WHERE OPP_ID = $optionalPriceId";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function findOptionItemBy($optionItemId){
    $query = "SELECT * FROM MITI_OPTION_ITEM WHERE OOI_ID = $optionItemId";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function findOptionalPriceByOptionItem($optionItemId){
    $query = "SELECT OPP_NAME FROM MITI_OPTIONAL_PRICE WHERE OPP_ID IN (SELECT OPP_ID FROM MITI_OPTION_ITEM WHERE OOI_ID = $optionItemId)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

// get list of optional price and first option item of each list item
function getOptionInfo($proId){
    $query = "SELECT OPP_ID, (SELECT OOI_ID FROM MITI_OPTION_ITEM OI WHERE OI.OPP_ID = COP.OPP_ID LIMIT 1) AS OOI_ID 
              FROM MITI_CATEGORY_OPTIONAL_PRICE COP 
              WHERE CAT_ID IN (SELECT CAT_ID FROM MITI_PRODUCT WHERE PRO_ID = $proId)";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}
?>
